package org.compiere.report;

import java.io.File;
import java.io.FileOutputStream;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Timestamp;
import java.text.SimpleDateFormat;
import java.util.Properties;
import java.util.logging.Level;

import org.adempiere.exceptions.AdempiereException;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.DataFormat;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.compiere.model.MAttachmentEntry;
import org.compiere.model.MBPartner;
import org.compiere.model.MClient;
import org.compiere.model.MOrg;
import org.compiere.process.ProcessInfo;
import org.compiere.process.ProcessInfoParameter;
import org.compiere.process.SvrProcess;
import org.compiere.util.DB;
import org.compiere.util.Env;
import org.compiere.util.Msg;
import org.eevolution.model.MHRJob;

import extend.org.compiere.utils.Util;

public class FI_Form_Inv4 extends SvrProcess {
	/*Current context*/
	private Properties m_ctx ;
	
	private ProcessInfo pi ;

	Timestamp p_DateDocBase, p_DateState, p_DateDoc;
	
	private int p_chiefComm, p_memberComm1, p_memberComm2, p_memberComm3, p_memberComm4, p_memberComm5;
	
	String ResultPath, p_DocumentNo;
	
	private boolean p_IsEmployee = false;
	
	/*
	* Prepare - e.g., get Parameters.
	*/
	@Override
	protected void prepare() {
	log.info( "");
	m_ctx = Env. getCtx();
	pi = getProcessInfo();
	ProcessInfoParameter[] para = getParameter();
	for (int i = 0; i < para.length; i++) {
		String name = para[i].getParameterName();
		if (para[i].getParameter() == null)
			;
		else if (name.equals("DocumentNo"))
			p_DocumentNo = (String) para[i].getParameter();
		else if (name.equals("DateDoc"))
			p_DateDoc = (Timestamp) para[i].getParameter();
		else if (name.equals("DateDocBase"))
			p_DateDocBase = (Timestamp) para[i].getParameter();
		else if (name.equals("DateState"))
			p_DateState = (Timestamp) para[i].getParameter();
		else if (name.equals("chiefComm"))
			p_chiefComm = para[i].getParameterAsInt();
		else if (name.equals("memberComm1"))
			p_memberComm1 = para[i].getParameterAsInt();
		else if (name.equals("memberComm2"))
			p_memberComm2 = para[i].getParameterAsInt();
		else if (name.equals("memberComm3"))
			p_memberComm3 = para[i].getParameterAsInt();
		else if (name.equals("memberComm4"))
			p_memberComm4 = para[i].getParameterAsInt();
		else if (name.equals("memberComm5"))
			p_memberComm5 = para[i].getParameterAsInt();
		else if (name.equals("IsEmployee"))
			p_IsEmployee = para[i].getParameterAsBoolean();
		else
			log.log(Level. SEVERE, "Unknown Parameter: " + name);
		}
	}
	
	@Override
	protected String doIt() throws Exception {
		//File path
		ResultPath = "Form_Inv12_" ;
		Integer pathI = 0;
		boolean flag = true ;
		while (flag) {
			File f = new File(Util.localFilePath + ResultPath + ".xlsx");
			try {
				if (f.exists() && !f.delete()) {
					ResultPath = ResultPath + "(" ;
					ResultPath = ResultPath .substring(0, 12) + (++pathI) + ")";
				} else {
					flag = false;
				}
			} catch (Exception e) {
				e.printStackTrace();
				ResultPath = ResultPath + "(" + (++pathI) + ")";
			}
		}
		ResultPath = Util.localFilePath + ResultPath + ".xlsx" ;
		MAttachmentEntry entry = Util. getAttachment(pi, m_ctx, ResultPath);
		if (entry == null)
			throw new AdempiereException(Msg.translate (m_ctx , "NotFoundTemplate"));

		Workbook wb = new XSSFWorkbook(entry.getInputStream());
		Sheet sheet = wb.getSheetAt(0);
		Row R = null;
		Cell C = null;
		
		//Cell Styling
		CellStyle mergeStyle = wb.createCellStyle();
		mergeStyle.setBorderBottom(( short)1);
		mergeStyle.setBorderTop(( short)1);
		mergeStyle.setBorderLeft(( short)1);
		mergeStyle.setBorderRight(( short)1);
		Font mergeFont = wb.getFontAt(sheet.getRow(9).getCell(3).getCellStyle().getFontIndex());
		mergeStyle.setFont(mergeFont);
		mergeStyle.setWrapText( true);
		mergeStyle.setVerticalAlignment(CellStyle. VERTICAL_CENTER);
		
		CellStyle dfcs = wb.createCellStyle();
		dfcs.setBorderBottom(( short)1);
		dfcs.setBorderTop(( short)1);
		dfcs.setBorderLeft(( short)1);
		dfcs.setBorderRight(( short)1);
		dfcs.setFont(mergeFont);
		dfcs.setWrapText( true);
		dfcs.setVerticalAlignment(CellStyle. VERTICAL_CENTER);
		DataFormat df = wb.createDataFormat();
		dfcs.setDataFormat(df.getFormat("0.00"));
		
		//Org
		sheet.getRow(9).getCell(0).setCellValue(MOrg.get(m_ctx, Env.getAD_Org_ID(m_ctx)).getName());
		sheet.getRow(9).getCell(57).setCellValue((new MBPartner(m_ctx , MOrg.get(m_ctx, Env.getAD_Org_ID(m_ctx )).getLinkedC_BPartner_ID(get_TrxName()), get_TrxName()).getFI_BIN_IIN()));
		
		sheet.getRow(14).getCell(49).setCellValue(p_DocumentNo);
		sheet.getRow(14).getCell(57).setCellValue((new SimpleDateFormat("dd.MM.yyyy").format(p_DateDoc)));
		
		//Document base
		sheet.getRow(17).getCell(21).setCellValue((new SimpleDateFormat("dd").format(p_DateDocBase)));
		sheet.getRow(17).getCell(24).setCellValue((new SimpleDateFormat("MMMMM").format(p_DateDocBase)));
		sheet.getRow(17).getCell(30).setCellValue((new SimpleDateFormat("yyyy").format(p_DateDocBase)));
		//Date state
		sheet.getRow(19).getCell(1).setCellValue((new SimpleDateFormat("dd").format(p_DateState)));
		sheet.getRow(19).getCell(4).setCellValue((new SimpleDateFormat("MMMMM").format(p_DateState)));
		sheet.getRow(19).getCell(10).setCellValue((new SimpleDateFormat("yyyy").format(p_DateState)));
		
		//Commissions
		String sqlfooter = "SELECT HR_Job_ID FROM HR_Employee e WHERE e.C_BPartner_ID = " + p_chiefComm;
		int idjob = DB.getSQLValue(get_TrxName(), sqlfooter);
		sheet.getRow(43).getCell(13).setCellValue((new MHRJob(m_ctx, idjob, get_TrxName()).getName()));
		sheet.getRow(43).getCell(32).setCellValue((new MBPartner(m_ctx, p_chiefComm, get_TrxName()).getName()));
		
		sqlfooter = "SELECT HR_Job_ID FROM HR_Employee e WHERE e.C_BPartner_ID = " + p_memberComm1;
		idjob = DB.getSQLValue(get_TrxName(), sqlfooter);
		sheet.getRow(45).getCell(11).setCellValue((new MHRJob(m_ctx, idjob, get_TrxName()).getName()));
		sheet.getRow(45).getCell(30).setCellValue((new MBPartner(m_ctx, p_memberComm1, get_TrxName()).getName()));
		
		sqlfooter = "SELECT HR_Job_ID FROM HR_Employee e WHERE e.C_BPartner_ID = " + p_memberComm2;
		idjob = DB.getSQLValue(get_TrxName(), sqlfooter);
		sheet.getRow(47).getCell(11).setCellValue((new MHRJob(m_ctx, idjob, get_TrxName()).getName()));
		sheet.getRow(47).getCell(30).setCellValue((new MBPartner(m_ctx, p_memberComm2, get_TrxName()).getName()));
		
		sqlfooter = "SELECT HR_Job_ID FROM HR_Employee e WHERE e.C_BPartner_ID = " + p_memberComm3;
		idjob = DB.getSQLValue(get_TrxName(), sqlfooter);
		sheet.getRow(49).getCell(11).setCellValue((new MHRJob(m_ctx, idjob, get_TrxName()).getName()));
		sheet.getRow(49).getCell(30).setCellValue((new MBPartner(m_ctx, p_memberComm3, get_TrxName()).getName()));
		
		sqlfooter = "SELECT HR_Job_ID FROM HR_Employee e WHERE e.C_BPartner_ID = " + p_memberComm4;
		idjob = DB.getSQLValue(get_TrxName(), sqlfooter);
		sheet.getRow(51).getCell(11).setCellValue((new MHRJob(m_ctx, idjob, get_TrxName()).getName()));
		sheet.getRow(51).getCell(30).setCellValue((new MBPartner(m_ctx, p_memberComm4, get_TrxName()).getName()));
		
		sqlfooter = "SELECT HR_Job_ID FROM HR_Employee e WHERE e.C_BPartner_ID = " + p_memberComm5;
		idjob = DB.getSQLValue(get_TrxName(), sqlfooter);
		sheet.getRow(53).getCell(11).setCellValue((new MHRJob(m_ctx, idjob, get_TrxName()).getName()));
		sheet.getRow(53).getCell(30).setCellValue((new MBPartner(m_ctx, p_memberComm5, get_TrxName()).getName()));
		
		int row = 28;
		double sumDr = 0, sumCr = 0;
		String isEmployee = "N";
		if (p_IsEmployee == true) isEmployee = "Y";
		PreparedStatement ps = null;
		ResultSet rs = null;
		StringBuffer sql = new StringBuffer("WITH t1 AS ( " + 
					"SELECT 	COALESCE(fa.C_BPartner_ID, 1) AS C_BPartner_ID " + 
						", cbp.Name AS BPartner " +  
						", cbp.FI_BIN_IIN " + 
						", ev.Value AS Account " + 
						", sum(fa.AmtAcctDr) AS Debet " + 
						", sum(fa.AmtAcctCr) AS Credit " + 
						", (sum(fa.AmtAcctDr) - sum(fa.AmtAcctCr)) AS Saldo " +  
					"FROM Fact_Acct fa " + 
					"INNER JOIN C_ElementValue ev ON ev.C_ElementValue_ID = fa.Account_ID " +  
					"LEFT JOIN C_BPartner cbp ON cbp.C_BPartner_ID = fa.C_BPartner_ID " +  
					"WHERE 	fa.AD_Client_ID = " + Env.getAD_Client_ID(m_ctx) + " " + 
						"AND fa.IsActive = 'Y' " + 
						"AND fa.PostingType = 'A' " + 
						"AND fa.C_AcctSchema_ID = " + MClient.get(m_ctx).getAcctSchema().get_ID() + " " + 
						"AND (cbp.isEmployee = 'N' OR cbp.isEmployee = '" + isEmployee + "') " + 
//						"AND ev.Value IN ('121000','122000','123000','124000','125001','125002','125003','125004','126000','128001','128002','128003','128004','129000') " + 
						"AND (ev.Value LIKE '12%' OR ev.Value LIKE '21%') " + 
						"AND fa.DateAcct <= '" + p_DateState + "' " + 
					"GROUP BY fa.C_BPartner_ID, cbp.Name, cbp.FI_BIN_IIN, ev.Value " +  
					"ORDER BY cbp.Name, ev.Value " +  
					"), t2 AS ( " + 
					"SELECT 	COALESCE(fa.C_BPartner_ID, 1) AS C_BPartner_ID " + 
						", cbp.Name AS BPartner " +  
						", cbp.FI_BIN_IIN " + 
						", ev.Value AS Account " + 
						", sum(fa.AmtAcctDr) AS Debet " + 
						", sum(fa.AmtAcctCr) AS Credit " + 
						", (sum(fa.AmtAcctDr) - sum(fa.AmtAcctCr)) AS Saldo " +  
					"FROM Fact_Acct fa " + 
					"INNER JOIN C_ElementValue ev ON ev.C_ElementValue_ID = fa.Account_ID " +  
					"LEFT JOIN C_BPartner cbp ON cbp.C_BPartner_ID = fa.C_BPartner_ID " +  
					"WHERE 	fa.AD_Client_ID = " + Env.getAD_Client_ID(m_ctx) + " " + 
						"AND fa.IsActive = 'Y' " + 
						"AND fa.PostingType = 'A' " + 
						"AND fa.C_AcctSchema_ID = " + MClient.get(m_ctx).getAcctSchema().get_ID() + " " + 
						"AND (cbp.isEmployee = 'N' OR cbp.isEmployee = '" + isEmployee + "') " + 
//						"AND ev.Value IN ('331000','332000','333000','334000','336000','337000','338000','339001','339002','339003','339004','339005','339006','339007') " + 
						"AND (ev.Value LIKE '33%' OR ev.Value LIKE '41%') " + 
						"AND fa.DateAcct <= '" + p_DateState + "' " +
					"GROUP BY fa.C_BPartner_ID, cbp.Name, cbp.FI_BIN_IIN, ev.Value " + 
					"ORDER BY cbp.Name, ev.Value " + 
					") ");
						
		try {
			ps = DB.prepareStatement(sql.append("select * from t1 where saldo != 0").toString(), get_TrxName());
			rs = ps.executeQuery();
			
			while(rs.next()) {
				//Shifting and Merging
				sheet.shiftRows(row, row + 28, 1);
				sheet.addMergedRegion(new CellRangeAddress(row, row, 0, 11));
				sheet.addMergedRegion(new CellRangeAddress(row, row, 12, 17));
				sheet.addMergedRegion(new CellRangeAddress(row, row, 18, 21));
				sheet.addMergedRegion(new CellRangeAddress(row, row, 22, 27));
				sheet.addMergedRegion(new CellRangeAddress(row, row, 28, 31));
				sheet.addMergedRegion(new CellRangeAddress(row, row, 32, 40));
				sheet.addMergedRegion(new CellRangeAddress(row, row, 41, 50));
				sheet.addMergedRegion(new CellRangeAddress(row, row, 51, 64));
				
				R = sheet.createRow(row);
				for(int i = 0; i <= 64; i++){
					if (i == 32) {
						C = R.createCell(i);
						C.setCellStyle(dfcs); 
					} else {
						C = R.createCell(i);
						C.setCellStyle(mergeStyle); 
					}
				}

				sheet.getRow(row).getCell(0).setCellValue(rs.getString("BPartner"));
				sheet.getRow(row).getCell(12).setCellValue(rs.getString("FI_BIN_IIN"));
				sheet.getRow(row).getCell(18).setCellValue(rs.getString("Account"));
//							sheet.getRow(row).getCell(22).setCellValue();
//							sheet.getRow(row).getCell(28).setCellValue();
				sheet.getRow(row).getCell(32).setCellValue(rs.getDouble("saldo"));
				sumDr += rs.getDouble("saldo");
//							sheet.getRow(row).getCell(41).setCellValue();
//							sheet.getRow(row).getCell(51).setCellValue();
				
				row++;
			}
		} catch (Exception e) {
			log.log(Level.SEVERE, sql.toString(), e);
		} finally {
			DB.close(rs, ps);
			rs = null;
			ps = null;
		}
		sheet.getRow(row).getCell(32).setCellValue(sumDr);
		row += 13;
		try {
			sql.replace(1490, 1492, "t2");
			ps = DB.prepareStatement(sql.toString() , get_TrxName());
			rs = ps.executeQuery();
			
			while(rs.next()) {
				//Shifting and Merging
				sheet.shiftRows(row, row + 28, 1);
				sheet.addMergedRegion(new CellRangeAddress(row, row, 0, 11));
				sheet.addMergedRegion(new CellRangeAddress(row, row, 12, 17));
				sheet.addMergedRegion(new CellRangeAddress(row, row, 18, 21));
				sheet.addMergedRegion(new CellRangeAddress(row, row, 22, 27));
				sheet.addMergedRegion(new CellRangeAddress(row, row, 28, 31));
				sheet.addMergedRegion(new CellRangeAddress(row, row, 32, 40));
				sheet.addMergedRegion(new CellRangeAddress(row, row, 41, 50));
				sheet.addMergedRegion(new CellRangeAddress(row, row, 51, 64));
				
				R = sheet.createRow(row);
				for(int i = 0; i <= 64; i++){
					if (i == 32) {
						C = R.createCell(i);
						C.setCellStyle(dfcs); 
					} else {
						C = R.createCell(i);
						C.setCellStyle(mergeStyle); 
					}
				}
				
				
				sheet.getRow(row).getCell(0).setCellValue(rs.getString("BPartner"));
				sheet.getRow(row).getCell(12).setCellValue(rs.getString("FI_BIN_IIN"));
				sheet.getRow(row).getCell(18).setCellValue(rs.getString("Account"));
//							sheet.getRow(row).getCell(22).setCellValue();
//							sheet.getRow(row).getCell(28).setCellValue();
				sheet.getRow(row).getCell(32).setCellValue(rs.getDouble("saldo"));
				sumCr += rs.getDouble("saldo");
//							sheet.getRow(row).getCell(41).setCellValue();
//							sheet.getRow(row).getCell(51).setCellValue();
				
				row++;
			}
		} catch (Exception e) {
			log.log(Level.SEVERE, sql.toString(), e);
		} finally {
			DB.close(rs, ps);
			rs = null;
			ps = null;
		}
		sheet.getRow(row).getCell(32).setCellValue(sumCr);
	
		//--------------------------------------------------------------------------
		//Write the output to a file
		FileOutputStream fileOut = new FileOutputStream(ResultPath);
		wb.write(fileOut);
		fileOut.close();
		
		//Open the file with the application associated
		Util.launchFile(new File(ResultPath));
		
		return Msg.translate(m_ctx, "Success");
	}	
}
